package jdbctools;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 用于两库数据比对
 * */
public class DataCheck {
	List<List<Object>> baosongList = new ArrayList<List<Object>>();
	List<List<Object>> fengkongList = new ArrayList<List<Object>>();
	
	String sql_baosong="SELECT CFR.OUTITEMCODE,CFR.REPORTITEMCODE,to_char(CF.REPORTITEMVALUE) REPORTITEMVALUE,to_char(CF.REPORTITEMWANVALUE) REPORTITEMWANVALUE,CF.TEXTVALUE,CFR.OUTITEMTYPE,CFR.OUTITEMNOTE,CF.DESTEXT,CFRE.PORTITEMCODE,CFRE.ELEMENTCODE,CFRE.CONTEXTCODE,CFRE.UNITCODE,CFRE.DECIMALS,CFRE.STARTORENDTYPE,CT.AXIS,CT.MEMBER,CT.LINEMEMBER,CT.REMARK,CF.ISUSED FROM CFREPORTITEMCODEDESC cfr left join (SELECT * FROM CFREPORTDATA WHERE 1 =1   AND reportid = '2017_2_2_0_20170714_86010100'  AND quarter = '2'  AND month = '2017'  and  comcode='000000' and reportrate = '2' ) cf  on cfr.REPORTITEMCODE = cf.Outitemcode  left join CfReportElement cfre on cfre.portitemcode=cfr.reportitemcode left join contexttable ct on ct.contextcode=cfre.contextcode WHERE  1 = 1 and cfre.cfreporttype='0' AND cfr.halfyearreport = '1'  AND cfr.Propertyinsurance1 = '1'  ORDER BY CFR.OUTITEMCODE ASC ";
	String sql_fengkong="SELECT CFR.OUTITEMCODE,CFR.REPORTITEMCODE,to_char(CF.REPORTITEMVALUE) REPORTITEMVALUE,to_char(CF.REPORTITEMWANVALUE) REPORTITEMWANVALUE,CF.TEXTVALUE,CFR.OUTITEMTYPE,CFR.OUTITEMNOTE,CF.DESTEXT,CFRE.PORTITEMCODE,CFRE.ELEMENTCODE,CFRE.CONTEXTCODE,CFRE.UNITCODE,CFRE.DECIMALS,CFRE.STARTORENDTYPE,CT.AXIS,CT.MEMBER,CT.LINEMEMBER,CT.REMARK,CF.ISUSED FROM CFREPORTITEMCODEDESC cfr left join (SELECT * FROM CFREPORTDATA WHERE 1 =1   AND reportid = '2017_2_2_0_20170630_86000000'  AND quarter = '2'  AND month = '2017'  and  comcode='000008' and reportrate = '2' ) cf  on cfr.REPORTITEMCODE = cf.Outitemcode  left join CfReportElement cfre on cfre.portitemcode=cfr.reportitemcode left join contexttable ct on ct.contextcode=cfre.contextcode WHERE  1 = 1 and cfre.cfreporttype='0' AND cfr.halfyearreport = '1'  AND cfr.Propertyinsurance1 = '1'  ORDER BY CFR.OUTITEMCODE ASC ";
	
	public static void main(String[] args) {
		DataCheck dc = new DataCheck();
		dc.queryData();
		dc.check();
	}

	public void queryData() {
		Statement s_baosong = null;
		ResultSet rs_baosong = null;
		
		Statement s_fengkong = null;
		ResultSet rs_fengkong = null;
		try {
			s_baosong = JDBCTools.getConnection("ltbaosong", "ltbaosong", "oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@localhost:1521:orcl").createStatement();
			rs_baosong = s_baosong.executeQuery("select * from ltbaosong.cfreportelement order by id asc");
			
			//"select * from ltbaosong.contexttable order by contextcode asc"
			
			pickData(rs_baosong,baosongList);
			
			rs_baosong.close();
			s_baosong.close();
			
			
			s_fengkong = JDBCTools.getConnection("lttianan", "lttianan", "oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@localhost:1521:orcl").createStatement();
			rs_fengkong = s_fengkong.executeQuery("select * from lttianan.cfreportelement order by id asc");
			
			//"select * from lttianan.contexttable order by contextcode asc"
			
			pickData(rs_fengkong,fengkongList);
			
			rs_fengkong.close();
			s_fengkong.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**捡出数据存到List*/
	public void pickData(ResultSet rs,List<List<Object>> list){
		list.clear();
		int columns = 0;
		try {
			while(rs.next()){
				columns = rs.getMetaData().getColumnCount();
				List<Object> row = new ArrayList<Object>();
				for(int i=1;i<=columns;i++){
					Object o = rs.getObject(i);
					row.add(o);
				}
				list.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 数据比对
	 * */
	public void check() {
		Object baosong = null;
		Object fengkong = null;
		for(int i=0;i<baosongList.size();i++){
			List<Object> row_baosong = baosongList.get(i);
			List<Object> row_fengkong = fengkongList.get(i);
			for(int j=0;j<row_baosong.size();j++){
				baosong = row_baosong.get(j);
				fengkong = row_fengkong.get(j);
				if(baosong!=fengkong && !baosong.equals(fengkong)){
					System.out.println("坐标：("+i+","+j+")"+"\n baosong:"+baosong+" ,\n fengkong:"+fengkong);
				}
			}
		}
	}
}
